<?php
/** Rho Database module.
*
* Normally this file should be require_once'd by a DB model class file which is required by a controller.
* E.g.
* app/models/widgets.php<code>
* require_once Rho::$PATH.'rho/db.inc.php';
* 
* class Widgets_Model extends DB_Model{
* ...
*}  
* </code>
*
* www/widgets.php<code>
* require_once '../rho/core.inc.php';
*
* class Widgets_Controller extends Rho_HTML_Controller{
*   private $model;
*   public $widgets;
*
*  public function  __construct(){
*    require_once Rho::$APP_PATH.'models/widgets.php';
*    $this->model=new Widgets_Model();
*  }
*
*  public function index(){
*    $this->title='Widgets List';
*    $this->description='A list of widgets';
*    $this->widgets=$this->model->fetch();
*    $this->loadView('widgets_list');
*  }
*}
*Rho::init('Widgets');
*</code>
* 
* Otherwise, if you require database functionality, require_once this file
* after requiring core.
*
* @package Rho
* @author Pete
* @version 0.1
* @license http://www.opensource.org/licenses/apache2.0.php Apache License, Version 2.0.*/

/** Thin Database abstraction class.
* 
* Normally Rho_DB is used by a DB_Model class, and not directly by a controller.
*
* Important is that:
* The database is set to use UTF-8 encoding.
* All Times and dates are stored as GMT.
* Field and table names should be lowercase, without spaces or other symbols ( _ is ok though )
*
*
* Note that Rho_DB does not attempt to rewrite SQL statements to the database server's 
* particular dialect - that would require a heavy weight abstraction layer such as ADODB.
* If you change database server types, you may have to rewrite the SQL statements (but not the function calls).
*
* That said, it will standardise date and time values (ISO 8601 format),
* and provides server-specific name and string escaping.  If you keep to standard SQL, you'll have fewer problems.
* For example, avoid vender-specific functions such as mySQL's NOW() - 
* instead use <code>$now=$db->esc(Rho::nowDate(), Rho_DB::DATE); </code>
*
* @see DB_Model
* @final
* @package Rho */
final class Rho_DB{

  /** Constant indicating a field.
  * @see where() */
  const FIELD=0;

  /** Constant indicating a string field type such as VARCHAR.
  * @see esc() */
  const STR=1;    
  
  /** Constant indicating a text field type such as TEXT.
  * @see esc() */
  const TEXT=3;
  
  /** Constant indicating a string field type that won't be converted by a model, such as VARCHAR or TEXT.
  * @see esc() */
  const RAW_STR=4;
  
  /** Constant indicating an integer field type such as INT.
  * @see esc() */
  const INT=8;
  
  /** Constant indicating an floating point number field type such as FLOAT.
  * @see esc() */
  const FLOAT=16;
  /** Constant indicating a date field type such as DATE.
  * @see esc() */
  const DATE=32;
 
  /** Constant indicating a time field type such as TIME.
  * @see esc() */
  const TIME=64;
 
 /** Constant indicating a date time field type such as TIMESTAMP.
  * @see esc() */
  const DATETIME=128;
  
  /** Constant indicating a binary field type.
  * @see esc() */
  const BIN=256;
  
  /** Constant indicating an boolean field type.
  * @see esc() */
  const BOOL=1024;
  
  /** Constant indicating an INNER JOIN.
  * @see DB_Model::$joins */
  const INNER=1;
  /** Constant indicating a LEFT JOIN.
  * @see DB_Model::$joins */
  const LEFT=2;
  /** Constant indicating a RIGHT JOIN.
  * @see DB_Model::$joins */
  const RIGHT=4;
  /** Constant indicating a FULL OUTER JOIN.
  * @see DB_Model::$joins */
  const FULL_OUTER=8;
  /** Constant indicating a CROSS JOIN.
  * @see DB_Model::$joins */
  const CROSS=16;

  /** @var array assocative array of DB instances.
  * key is the name, value is the DB driver object.*/
  private static $_instances=array();
  
  /** @var string*/  
  private $_name;
  
  /** @var string The type of database. */
  private $_type;
  
  /** @var Rho_DB_Driver object.*/
  private $_driver=NULL;
  
  /** @var string Table prefix
  private $_prefix;
  
  /** @var string Name of the driver class.*/
  private $_class_name;

  /** Produces an instance of the database class.
  * @final
  * @param string $name The name of the database connection settings from the config file to use - defaults to 'default'.
  * @return DB A database instance.*/
  public final static function & factory($name='default'){
    if(isset(self::$_instances[$name]) ){
      return self::$_instances[$name];
    }
    self::$_instances[$name]=new Rho_DB($name);
    return self::$_instances[$name];
  }

  /** Private constructor.
  * @final
  * @param $name The name of the connection which determines which config settings to use.
  * Default is 'default' */
  private final function __construct(& $name){
    $this->_name=$name;
    $this->_type=Rho::conf('db.'.$name.'.type', 'mysql');
    // special case for mysql - dynamically determine whether we use the new or the old driver.
    if( $this->_type=='mysql' ){
      if(extension_loaded('mysqli') ){
         $this->_type='mysqli';
      }
      else {
        $this->_type='mysql_old';
      }
    } # end mysql special case
    $this->_prefix=Rho::conf('db.'.$name.'.prefix', '');
  }
  
  /** Loads the necessary DB driver.
  * @final*/
  private final function _loadDriver(){
    if($this->_driver!=NULL ) return; # already loaded
    $this->_class_name='DB_'.$this->_type.'_Driver';
    if($this->_type != 'mysqli'){ # mysqli included in this file
      require_once Rho::$PATH.'rho/db/'.$this->_class_name.'.class.php';
    }
    $class_name=$this->_class_name;
    $this->_driver=new $class_name($this->_name);
  }

  /** @return string The type of database connection.*/
  public final function getType(){
    return $this->_type;
  }
  
  /** @return string The name of database instance - e.g. 'default' */
  public final function getName(){
    return $this->_name;
  }
  
  /** @ignore */
  function __destruct() {
    #echo ' DB object dying';
    if($this->_driver!=NULL ){
      unset($this->_driver);
      $this->_driver=NULL;
    }
  }

  /** Execute a SQL query.
  * You MUST use the select() method instead for SELECT queries.
  *
  * Normally this method is called by DB_Model class and not directly.
  * @param string &$sql A SQL statement that MUST have already been escaped (field names, strings, etc.).
  * @param string $table The table name this query effects.
  * @param bool $clear_cache If the query is successful, should the cache for the table be cleared - default TRUE.
  * @return int|bool Number of affected rows (which may be 0) or FALSE.
  * @see select(), DB_Model, directSelect()
  * @throws Rho_Exception if there's a database error.
  * @throws DB_Connection_Exception If there's a connection error.*/  
  public function query(& $sql, $table='', $clear_cache=TRUE){
    if($this->_driver==NULL) $this->_loadDriver();
    $q=$this->_driver->query($sql);
    if($q===FALSE){
      return FALSE;
    }
    $i=$this->_driver->affectedRows($q);
    $this->_driver->freeResult($q);
    return $i;
  }
  
  /** Execute a SQL SELECT query directly.
  * Not for general use - normally use a DB_Model object, which calls select() .
  * @param string &$sql A SQL statement that MUST have already been escaped (field names, strings, etc.).
  * @param array &$field_defs An asssociative array of fields and their types.
  * @param string $record_class
  * @return DB_Rowset|bool A database result object or FALSE.
  * @see select(), DB_Model
  * @throws Rho_Exception if there's a database error.
  * @throws DB_Connection_Exception If there's a connection error.*/ 
  public function directSelect(& $sql, array & $field_defs, $record_class='DB_Record'){
    if($this->_driver==NULL) $this->_loadDriver();
    $q=$this->_driver->query($sql);
    if(! $q){
      return FALSE;
    }
    $m=NULL;
    return new DB_Rowset($this, $q, $this->_driver->numRows($q), $field_defs, $m, $record_class);
  }
  
  /** Execute a SQL SELECT from a model.
  * @throws Rho_Exception if there's a database error.
  * @throws DB_Connection_Exception If there's a connection error.*/
  public function select(string & $sql, & $model){
    if($this->_driver==NULL) $this->_loadDriver();
    $q=$this->_driver->query($sql);
    if(! $q){
      return FALSE;
    }
    return new DB_Rowset($this, $q, $this->_driver->numRows($q), $model->getFields(), $model, $model->getRecordClass());
  }
  
  /** Returns the database driver instance being used.
  * Used internally by DB_Rowset.
  * @access private
  * @return Rho_DB_Driver The database driver for this database.
  * @see DB_Rowset */
  public function & driver(){
    if($this->_driver==NULL) $this->_loadDriver();
    return $this->_driver;
  }
  
  /** Escapes a field name.
  * @param string $fieldname The name of the field with or without the table name. e.g. 'foo' or 'widgets.foo'.
  * May be '*'.
  * @param string $default_table The table name to use when $fieldname doesn't contain a table name.
  * @return string The escaped field name. e.g.  `widgets`.`foo` .
  * @see esc(), table() */
  public function field($fieldname, $default_table=''){
    if($this->_driver==NULL) $this->_loadDriver();
    $parts=explode('.', $fieldname, 2);
    if( ! empty($default_table) ){
      $default_table=$this->_prefix.$default_table;
    }
    if(count($parts)==1 ){
      return $this->_driver->escapeField($fieldname, $default_table);
    }
    return $this->_driver->escapeField($parts[1], $parts[0]);
  }
  
  /** Escape a table name.
  * @param string $tbalename The name of the table to escape.
  * @return string The escaped table name. E.g.  `widgets` .
  * @see field(), esc() */
  public function table($tablename){
    if($this->_driver==NULL) $this->_loadDriver();
    $tablename=$this->_prefix.$tablename;
    return $this->_driver->escapeTable($tablename);
  }
  
  /** Escape a field value.
  * Example:
  * <code>$safe_string=$db->esc("Isn't it good?", Rho_DB::STR);
  * #returns something like 'Isn\'t it good?'</code>
  *
  * Note that string values (and dates, times, etc,) 
  * should NOT be already enclosed by quotes - this will be done by this method as required.
  * This is WRONG:
  * <code>$wrong_string=$db->esc("'".$my_string."'", Rho_DB::STR);
  * # will return something like '\'my string\''</code>
  *
  * May require an active database connection - will attempt to connect if this is the case.
  * @param string|int|bool $value the value to escape
  * @param int $type The field type of the value to escape
  * Use the constants defined -  e.g. Rho_DB::STR .
  * @return string The escaped value.
  * @see field(), table() 
  * @throws Rho_Exception if there's a database error.
  * @throws DB_Connection_Exception If there's a connection error.*/
  public function esc($value, int $type){
    if($this->_driver==NULL) $this->_loadDriver();
    switch($type) :
      case self::STR : return $this->_driver->escapeString($value);
      case self::TEXT : return $this->_driver->escapeString($value);
      case self::RAW_STR : return $this->_driver->escapeString($value);
      case self::INT : return round($value);
      case self::FIELD : return $this->field($value);
      case self::FLOAT : return ((float) $value);
      case self::DATE : return $this->_driver->escapeDate($value);
      case self::TIME : return $this->_driver->escapeTime($value);
      case self::DATETIME :return $this->_driver->escapeDateTime($value);
      case self::BIN : return $value;
      case self::BOOL : return $value ? 'TRUE' : 'FALSE' ;
      default: throw new Rho_Exception("Invalid type of $type passed to Rho_db->esc()", 7001);
    endswitch;
  }
  
  /** Produces an escaped SQL snippet for use in a WHERE statement.
  *
  * Does not include the WHERE keyword, so you can combine snippets.
  * E.g.<code>
  * $sql.='WHERE'.$db->where('foo', $bar, Rho_DB:STR, 'widgets').'AND'.$db->where('qty', '0', Rho_DB:INT, '>');
  * #  ~ ' WHERE `widgets`.`foo`='beispiel' AND `qty`>0 ' 
  * </code>
  *  
  * You can also use this method for generating JOIN statements.
  * E.g. <code>
  *  $sql .= 'LEFT JOIN '.$db->table('foos').' ON'.$db->where('bar_id','bars.foo', Rho_DB::FIELD, 'foos');
  * # ~ 'LEFT JOIN `foos` ON `foos`.`bar_id`=`bars`.`foo`
  * </code>
  *
  * May require an active database connection - will attempt to connect if this is the case.
  * @param string $fieldname The name of the field with or without the table name.
  * @param string|int|bool $value the value to escape
  * @param int $value_type The type of the value
  * Use the constants defined -  e.g. Rho_DB::STR .
  * When $value is a field name, pass Rho_DB::FIELD .
  * @param string $operator The comparison operator, such as = , !=, > , etc. Default is = 
  * @param string $table The table name to use when $fieldname doesn't contain a table name -  may be empty.
  * @return string The escaped snippet NOT including the WHERE statement with a leading and a trailing space.
  * Something like: `table`.`field`='$value' 
  * @see table(), esc(), field(), limit()
  * @throws Rho_Exception if there's a database error.
  * @throws DB_Connection_Exception If there's a connection error.*/
  public function where($fieldname, $value, $value_type, $operator='=', $table=''){
    if($this->_driver==NULL) $this->_loadDriver();
    if($value!=NULL){
      return ' '.$this->field($field, $table).$operator.esc($value, $value_type).' ';
    }
    if($operator=='=' ){
      return $this->field($field, $table).' IS NULL';
    }
    else { 
      return $this->field($field, $table).' IS NOT NULL';
    }
  }
  
  /** Produce a driver specific LIMIT snippet for a SELECT statement.
  * @param int $qty Number of records to fetch.
  * @param int $offset Starting at this row number, where the firt row is 0.
  * @return string Something similar to LIMIT $offset, $qty 
  * @see where() */
  public function limit($qty, $offset=NULL){
    if($this->_driver==NULL) $this->_loadDriver();
    return $this->_driver->limit($qty, $offset=NULL);
  }
  
}

/** A result of a database select query.
*
* Instances of this class are returned by Rho_DB->select() and  Rho_DB->directSelect().
* It contains DB_Record objects.
*
* You can interate the rowset so: <code>
* $rows=$model->fetch();
* foreach($rows as $row) :
*    echo $row->id;
* endforeach;
* unset($rows);
* </code>
*
* Note that serialising DB_Record will call rewind().
* On unserialising, current() will always be the first record in the rowset.
*
* @see Rho_DB::select(), DB_Model, DB_Record, current()
* @package Rho
* @final
* @subpackage Rho-database */
final class DB_Rowset implements Iterator, Countable{

  /** @var Rho_DB_Driver DB driver instance, or NULL when created from a cached result*/
  private $_driver=NULL;
  
  /** @var DB_Model The model creating the rowset, or NULL if from Rho_DB->directSelect() */
  private $_model;

  /** @var resource The result resource when from a query.*/
  private $_result;
  
  /** @var int number of rows >=0 */
  private $_qty=0;
  
  /** @ var int Pointer to current record, or -1 when no results. >=-1 */
  private $_ptr=-1; 
  
  /** @var string Name of the record class to use - defaults to DB_Record.*/
  private $_record_class;
  
  /** @var DB_Record The current row object, or NULL for an empty rowset */
  private $_current;
  
  /** @var array asscoiative array of field type definitions.
  * Key is field name, value is an int matching the Rho_DB constants*/
  private $_field_defs;
  
  /** @var array Contains the rows if rowset is from cache.*/
  private $_rows=NULL;
  
  /** Creates a rowset from a query result.
  * @access private
  * @see Rho_DB::select(), Rho_DB::directSelect() */
  public function __construct(& $db, & $result, $qty=0, array & $field_defs, & $model=NULL,  & $record_class='DB_Record'){
    $this->_driver=& $db->driver();
    $this->_model=$model;
    $this->_result=$result;
    $this->_qty=$qty;
    $this->_field_defs=& $field_defs;
    $this->_record_class=$record_class;
    if($qty>0){
      $this->_ptr=0;
      $this->_nextRecord();
    }
  }
  
  /** Sets the current record to the next record
  * When this rowset is the result of a query, will call _fetchRow().
  * When from a cached result, sets _current to _row[_ptr] .
  * @uses _fetchRow()
  * @uses $_current
  * @uses $_rows */
  private function _nextRecord(){
    if($this->_driver!=NULL){
      $this->_fetchRow();
      return;
    }
    $this->_current=$this->_rows[$this->_ptr];
  }
  
  /** Fetches the next row for the result (when rowset was not created from a cached result).
  * Converts string and date/time fields.
  * @uses $_field_defs
  * @uses $_current
  * @uses $_result
  * @uses $_record_class
  * @uses $_model
  * @uses $_driver
  * @uses _cleanString() */  
  private function _fetchRow(){
    $this->_current=$this->_driver->fetchObject($this->_result, $this->_record_class, $this->_model );
    foreach(get_object_vars($this->_current) as $field=>$value) :
      if(array_key_exists($field, $this->_field_defs) ){
        switch($this->_field_defs[$field]) : 
          case Rho_DB::STR : $this->_cleanString($field); break;
          case Rho_DB::TEXT : $this->_cleanString($field); break;
          case Rho_DB::RAW_STR : $this->_cleanString($field); break;
          case self::INT :  break;
          case Rho_DB::DATE : if($this->_current->$field!=NULL ){ 
              $this->_driver->convertDate($this->_current->$field); }
              break;
          case Rho_DB::TIME : if($this->_current->$field!=NULL ){ 
              $this->_driver->convertTime($this->_current->$field); }
            break;
          case Rho_DB::DATETIME : if($this->_current->$field!=NULL ){ 
              $this->_driver->convertDateTime($this->_current->$field); } 
            break;
          case self::FLOAT :  break;
          case self::BIN :  break;
          case self::BOOL : break;
          case self::FIELD :  
            Rho_Log::info("Strange - DB_Rowset field definition is FIELD for $field \n".
              Rho_Log::backtrace(debug_backtrace()) );
            break;
          default: throw new Rho_Exception("DB_Rowset has invalid field definition type of ".$this->_field_defs[$field]. ' for field '.$field, 7002);
        endswitch;
      }
    endforeach; 
  }
  
  /** Cleans a string field of the current record when the field type is STR, TEXT or RAW_STR */
  private final function _cleanString(& $field){
     if($this->_current->$field!=NULL ){
       $this->_driver->cleanString($this->_current->$field);
       return;
     }
     $this->_current->$field='';
  }
  
  /** Rewinds the internal pointer. */
  public function rewind() {
     if($this->_qty < 1) {
       $this->_ptr=-1;
       return;
     }
     $this->_ptr=0;
     if($this->_driver!=NULL) {
       $this->_driver->seek($this->_result, 0);
       $this->_nextRecord();
     }
     else{
       $this->current=$this->_rows[0];
     }
  }

  /** Returns the current row. 
  * @return DB_Record A single record or NULL if the record set is empty.*/
  public function current() {
    if($this->_qty > 0 ){
      return $this->_current;
    }
    return NULL;
  }
  
  /** @ignore */
  public function key() {
    return $this->_ptr;
  }

  /** Moves the internal pointer to the next row if not at the last row.
  * @see current(), rewind() */
  public function next() {
    $this->_ptr++;
    if($this->_ptr<$this->_qty ){
      $this->_nextRecord();
    }
  }
  
  /** @ignore */
  public function valid() {
    return ($this->_ptr<$this->_qty && $this->_ptr>-1);
  }

  /** Returns the number of rows in the rowset.
  * @return int */  
  public function count(){
    return $this->_qty;
  }
   
  /** @ignore */
  public function __sleep(){
    if($this->_driver!=NULL){ # we're a live result set (as expected) so we need to set up rows
      $rows=array();
      $this->rewind();
      foreach($this as $row) :
        $rows[]=$row;
      endforeach;
      $this->_rows=rows();    
    }    
    $this->rewind;
    /* we can't store the references to the model, driver and field_defs,
     nor do we need to after setting _rows */
    return array('_rows', '_qty', '_record_class');
  }

  /** @ignore */    
  public function __wakeup(){
    if($this->_qty>0){
      $this->_ptr=0;
      $this->current=$this->_rows[0];
    }
    else{
      $this->current=NULL;
    }
    $this->_driver=NULL;
  }
  
  /** Sets the model after unserialising. 
  * @ignore */
  public function _setModel(& $model){
    if($this->_driver==NULL && $this->_rows!=NULL){ // should be the case
      $this->_model=& $model;
      foreach($this->_rows as $row) :
        $row->_setModel($model);
      endforeach;
      return;
    }
    throw new Rho_Exception(7150, 'DB_Rowset->_setModel() called, but Rowset is not from cache');
  }
    
  /** @ignore */
  function __destruct() {
    if($this->_driver!=NULL ){
      #echo 'DB Rowset dying !';
      $this->_driver->freeResult($this->_result);
      unset($this->_driver);
    }
  }
}

/** A single database record.
*
* Will be returned for a DB_Rowset.
* @see DB_Rowset::curent()
* @package Rho
* @subpackage Rho-database */
class DB_Record{
  
   private $_fields=array();
   private $_model;

   /** Creates a new record object - uses by DB_Rowset.
   * @access private
   * @param DB_Model &$model The model object for this Record or NULL.
   * @see DB_Rowset_fetchRow(), Rho_DB_Driver::fetchObject() */   
   public function __construct(& $model=NULL){
     $this->_model=& $model;
   }
   
   /** @ignore */
   function __set ($name, $value){
     $this->_fields[$name]=$value;
   }
   
   /** @ignore */
   function __get($name){
     if(array_key_exists($name, $this->_fields) ){
       return $this->_fields[$name];
     }
     trigger_error("Property $name does not exist", E_USER_NOTICE);
     return NULL;
   }
   
   /** @ignore */
   function __isset($name){
     return isset($this->_fields[$name]);
   }
   
    /** @ignore */
   function __unset ($name){
     unset($this->_fields[$name]);
   }
   
   
   /** @ignore */
  public function __sleep(){
    return array('_fields');
  }

  /** @ignore */    
  public function __wakeup(){
    $this->_model=NULL;
  }
  
   /** Sets the model after unserialising. 
  * @ignore */
  public function _setModel(& $model){
    if($this->_model==NULL ){ // must be the case
      $this->_model= & $model;
      return;
    }
    throw new Rho_Exception(7151, 'DB_Record->_setModel() called, but Record already has a model');
  }
   
   public function save(){
   
   }
   
   public function delete(){
   
   }
   
}

/** A Model that uses a database table for storage.
*
* This class is abstract, and so must be extended from.
*
* @subpackage Rho-database
* @package Rho .*/
abstract class DB_Model implements Rho_Model{

  /** @var string The name of the table this model uses - MUST be overidden by child classes.*/
  protected $table='';
  /** @var string The name of the database connection to use - defaults to 'default' */  
  protected $database_name='default';
  /** @param Rho_DB The data base connectin object - automatically initialised by the constructor.*/
  protected $db;
  /** @param array The fields of the this model's table including the primary key(s) - MUST be overidden by child classes.
  * Associative array, where the key is the name of the field, 
  * and the value is an integer indicating the field data type - e.g. Rho_DB::STR */
  protected $fields=array(
    'id'=>Rho_DB::INT,
    'name'=>Rho_DB::STR
  );
  
  /** @param array List of fields that are fetched from the table when retreiving a record from the table.
  * MUST be overidden by child classes.
  * Fields from related tables can be listed, by prefixing the table name - e.g. 'foreign_table.foo'. */ 
  protected $fetch_fields=array('id', 'name', 'category');
  /** @param array List of fields that are fetched from the table when retreiving multiple record from the table.
  * MUST be overidden by child classes.
  * Fields from related tables can be listed, by prefixing the table name - e.g. 'foreign_table.foo'. */ 
  protected $list_fields=array('id', 'name', 'categories.name');
  /** @param array Definiton of related tables (if any).*/
  protected $joins=array(
    'categories'=>array(
      'fk'=>'category',      
      'pk'=>'id',
      'type'=>Rho_DB::INNER,
      'on'=>NULL
    )
  );
  /** @param array The field data types of related tables. 
  * Associative array, where the key is the name of the field prefixed by the foriegn table name and an underscore, 
  * and the value is an integer indicating the field data type - e.g. Rho_DB::STR
  * @see $joins */
  protected $external_field=array(
    'category_name'=>Rho_DB::STR
  );
  /** @param string|array The name of the primary key -defaults to 'id'.
  * When the table uses composite primary keys, this must be an array listing the primary keys,
  * and $pk_is_composite must be set to TRUE.
  * @see $pk_is_composite */
  protected $pk='id';
  /** @param bool Whether the table's primary key auto increments or not - default TRUE.
  *  When the table uses composite primary keys, this must be set to FALSE. */
  protected $pk_autoincrement=TRUE;
  /** @param bool Whether the table uses composite primary keys, default is FALSE.
  * When TRUE, $pk must be an array.
  * @see $pk*/
  protected $pk_is_composite=FALSE;
  
  protected $name_field='name';
  
  protected $limit=12;
  
  protected $sort='id';
  protected $sort_dir='ASC';
  
  protected $cache=1000;

  protected $cache_get=FALSE;
  
  protected $cache_list_all=FALSE;
  


  
  /**@var string A default where statement (excluding the word WHERE) to be used on in statements - default is NULL. 
  * Should be set in the constructor and properly escaped
  * e.g. <code>class News_Model extends DB_Model{
  *  public __construct(){
  *    parent::__construct();
  *    # only show approved news items by default
  *    $this->where=$this->db->where('approved', 1, Rho_DB::INT);
  *  }
  *} </code>*/
  protected $where=NULL;
  
  /** Constructer - child classes MUST overide this AND call parent::__construct().*/
  public function __construct(){
    $this->db=Rho_DB::Factory($this->$database_name);
    if(Rho::conf('dev_mode') ){
      // disable caching when in development
      $this->cache=0;
      $this->cache_get=FALSE;
      $this->cache_list_all=FALSE;
    }
  }
  

  /** Fetches a single record.
  * 
  * Will use the cache if $this->cache_get is TRUE, and $fields is NULL (i.e use the default fetch fields).
  *
  * @param int|string|array The primary key value of the record to fetch.
  * If the table uses composite primary keys, this must be an associative array
  * @param array Optional - list of fields to retrieve. Normally $this->fetch_fields are retreived
  * @return DB_Record|bool A database record, or FALSE if not found.
  * @uses $cache
  * @uses $cache_get
  * @uses $fetch_fields
  * @uses id_oid()
  * @uses $where
  * @uses fields()
  * @uses pkWhere() */
  public function get($id, $fields=NULL){
    if($this->cache && $this->cache_get && $fields==NULL ){ # try the cache
      $oid=$this->id_oid('get', $id);
      require_once Rho::$PATH.'rho/cache.inc.php';
      $result=Rho_Cache::load($this->prefix(), $oid, $this->cache);
      if($result){
        $result->_setModel($this); # Need to set the model after fetched from cache
        return $result;
      }
      // result not in cache
      $use_cache=TRUE;
    }
    else {
      $use_cache=FALSE;
    }
    
    if($fields==NULL) $fields=$this->$fetch_fields;
    $sql='SELECT '.$this->fields($fields).' WHERE '.$this->pkWhere($id);
    
    if($this->where!=NULL){
      $sql .= ' AND '.$this->where;
    }
    
    $rowset=$this->db->select($sql.$this->db->limit(1), $this);
    if(! $rowset) return FALSE;
    
    if(count($rowset)==1 ){  
      $result=$rowset->current();
      if($use_cache ){
        Rho_Cache::save($this->prefix(), $oid, $result, $this->cache); 
      }
      return $result;
    }
    return FALSE;
  }
  
  
  /** Generates a cache object identifier for an action with a primary key value.
  * @param string $method The name of the method calling id_oid in lowercase.
  * @param int|string|array &$id The primary key values.
  * @return string An oid- something like: get_5
  * @see Rho_Cache */
  protected final function id_oid($method, & $id){
    if(! $this->$this->pk_is_composite){
      return $method.'_'.$id;
    }
    return $method.'_'.implode('_', $id);
  }
  

  /** Generates a SQL snippit to be used a WHERE statement using the primary key.
  * Used internally by other methods.
  *
  * e.g.<code>$id=5;
  * $sql .= 'WHERE'.$this->pkWhere($id); # will return something like: `id`=5
  *
  * $id=array('order_id'=>7, 'item_id'=>19);
  * $sql .= 'WHERE'.$this->pkWhere($id);# will return something like: `order_id`=7 AND `item_id`=19
  * </code>
  *
  * @param int|string|array &$id The primary key value.
  * If the table uses composite primary keys, this must be an associative array
  * where the keys are the names of the primary keys, and the values are the values to use.
  * @return string The snippet NOT including the word WHERE.*/
  protected final function pkWhere(& $id){
    if(! $this->pk_is_composite ){
      return $this->db->where($this->pk, $id, $this->fields[$this->pk]);  
    }
    // composite primary key - $id must be assoc. array
    if(! is_array($id) ){
      throw new Rho_Exception(7101, 'Composite primary key - $id must be an array for '.get_class($this));
    }
    $first=TRUE;
    $result='';
    foreach($this->pk as $pk) :
      if(! $first){
        $result .= 'AND';
      }
      else {
        $first=FALSE;
      }
      $result .= $this->db->where($pk, $id[$pk], $this->fields[$pk]);
    endforeach;
    return $result;
  }
  
  /** Generates a list of fields and if necessary JOIN statemnts for use in a select statement.
  * @param array &$fields A list of field names.
  * @return string The escaped field names, the FROM statement, and any required JOIN statements.
  * Does not include the word 'SELECT'.
  * @uses $joins
  * @uses $table */
  protected final function fields(& $fields){
    $not_first=FALSE;
    $result='';
    $joins='';
    $joined_tables=array();
    foreach($fields as $field) :
      if(! $not_first ){
        $result .= ', ';
      }
      else { $not_first=TRUE; }
      $parts=explode('.', $field, 2);
      if(count($parts)==1 ){
        $result .= $this->db->field($field, $this->table);
      }
      // foreign table - trasnform name and add join statment
      else { 
        $join=Rho::ifset($this->joins, $parts[0], FALSE); #get the join
        if(! $join) throw new Rho_Exception(7102, 'Missing joins definition in '.get_class($this).' for '.$parts[0]);
        // foos.field AS foo_field
        $result .= $this->db->field($parts[1], $parts[0]).' AS '.$this->db->field($join['fk'].'_'.$parts[1]);
        // if not already joined, create the join statement
        if(! in_array($part[0], $joined_tables) ){
          if(isset($join['on']) ){ // on statemnt already set
            $joins .= $join['on'];
          }
          else { // create JOIN statement dynamically
            switch($join['type']) :
              case Rho_DB::INNER : $joins.='INNER'; break;
              case Rho_DB::LEFT : $joins.='LEFT OUTER'; break;
              case Rho_DB::RIGHT : $joins.='RIGHT OUTER'; break;              
              case Rho_DB::FULL_OUTER : $joins.='FULL OUTER'; break;
              case Rho_DB::CROSS: $joins.='CROSS'; break; 
              default: 
              throw new Rho_Exception(7103, 'Unknown JOIN type of '.$join['type'].' in '.get_class($this).' for '.$parts[0]);
            endswitch;
            $joins .= ' JOIN '.$this->db->table($parts[0]);
            if($join['type']!=Rho_DB::CROSS ){ # no predicate on CROSS JOINs
              $joins .= ' ON'.$this->db->where($join['pk'], $this->table.'.'.$join['fk'], Rho_DB::FIELD, $parts[0]);
            }
          } # end dynmic join
          $joined_tables[]=$parts[0];
        } #end if not already in joined table
      } #end else foreign table
    endforeach;
    return $result.' FROM '.$this->db->table($this->table).' '.$joins;
  }
  
  /** Returns a single record, suitable for use in an edit form.
  *
  * Will retreive all fields (using *) , and NO related table fields.
  * Does not use the cache.
  * @param int|string|array The primary key value of the record to fetch.
  * If the table uses composite primary keys, this must be an associative array
  * where the keys are the names of the primary keys, and the values are the values to use.
  * @return DB_Record|bool The record, or FALSE if not found.
  * @throws Rho_Exception if there's a database error.
  * @throws DB_Connection_Exception If there's a connection error.
  * @see get()
  * @uses pkWhere()
  * @uses $where */
  public function getForEdit($id){
    $sql='SELECT '.$this->db->field('*').
    ' FROM '.$this->db->table($this->$table).
    ' WHERE';
    
    if($this->where!=NULL){
      $sql .= $this->where.' AND';
    }
    
    $sql .= $this->pkWhere($id)->$this->db->limit(1);
    $rowset=$this->db->select($sql, $this);
    if($rowset && $rowset->count()==1){
      return $rowset->current();
    }
    unset($rowset);
    return FALSE;
  }
  
  public function fetch($page=1, $sort=NULL, $sort_dir=NULL, $qty=NULL, $fields=NULL){
  
  }
  
  protected function select($fields, $sort, $sort_dir, $limit=NULL, $offset=NULL){
  
  }
  
  public function fetchAll($sort=NULL, $sort_dir=NULL, $fields=NULL){
  
  }
  
  public function update($id, & $data){
    
  }
  
  /** Creates a new record.
  * @param array $data Associative array, where the key is the field name and the value is the field data.
  * @return int|string|array The primary key of the new record.
  * @uses beforeCreate()
  * @uses onCreate() */
  public function create($data){
    $this->beforeCreate($data);
    $id=1;
    $this->onCreate($id, $data);
    return $id;
  }
  
  public function delete($id){
  
  }
  
  protected function beforeUpdate($id, & $data){
    // nothing
  }  
  
  protected function onUpdate($id, & $data){
    // nothing
  }
  
  protected function beforeDelete($id){
    // nothing
  }  
  
  protected function onDelete($id){
    // nothing
  }
  
  /** Called by create() before the record is inserted.
  * By default, this method does nothing - override it if you need to change field data before insertion.
  * @param array &$data Associative array, where the key is the field name and the value if the field data.*/
  protected function beforeCreate(& $data){
    // nothing
  }  
  
  /** Called by create() after the record is inserted.
  * By default, this method does nothing - override it if you need to do something like changing rows in a related table.
  * @param $id int|string|array The primary key of the newly created record.
  * @param array &$data Associative array, where the key is the field name and the value if the field data.*/
  protected function onCreate($id, & $data){
    // nothing
  }
  
  /** Return the cache prefix for this model.
  * @return string 'db.' + database name + '.' + table name .
  * @final
  * @see Rho_Cache */
  protected final function prefix(){
    return 'db.'.$this->$database_name.'.'.$this->$table;
  }
  

  /** Returns the fields of this model and any foreign table fields with their field type.
  * @return array An associative array where the key is the field name
  * and the value is an int indicating the field type (e.g. Rho_DB::STR ).*/
  public final function getFields(){
    return array_merge($this->fields, $this-$external_field);
  }
  
  /** Returns the name of the record class this model returns - normally 'DB_Record'.
  * @return string */
  public function getRecordClass(){
    return 'DB_Record';
  }
  
}

/** A database connection exception.
*
* This is a critical exception, so calling Rho::log() on an instance of this class will halt the code.
*
* @see Rho::log(), Rho_Critical_Exception
* @package Rho
* @subpackage Rho-database */
class DB_Connection_Exception extends Rho_Critical_Exception{

  /** Creates a database connection exception.
  * @param string $message Error message.
  * @param int code Database Error code, as reported from the database driver.
  * @param Exception $previous */
  public function __construct($message='Database connection Error', $code=7500, Exception $previous=NULL){
    parent::__construct($message, ($code+7000), $previous, 503, 'Service Unavailable');
  }
}